--region [dbo].[AddActivityNews]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddActivityNews]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddActivityNews]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddActivityNews]
GO

CREATE PROCEDURE [dbo].[AddActivityNews]
	@Title nvarchar(255),
	@Introduction nvarchar(4000),
	@Body nvarchar(max),
	@StartTime datetime,
	@EndTime datetime,
	@area nvarchar(255),
	@Dimensions nvarchar(255),
	@HasImage int,
	@ImagePath nvarchar(255),
	@Keyword nvarchar(50),
	@Source nvarchar(50),
	@UpdateTime datetime
AS

INSERT INTO [ActivityNews] (
	[Title],
	[Introduction],
	[Body],
	[StartTime],
	[EndTime],
	[area],
	[Dimensions],
	[HasImage],
	[ImagePath],
	[Keyword],
	[Source],
	[UpdateTime]
) VALUES (
	@Title,
	@Introduction,
	@Body,
	@StartTime,
	@EndTime,
	@area,
	@Dimensions,
	@HasImage,
	@ImagePath,
	@Keyword,
	@Source,
	@UpdateTime
)

GO
--endregion


--region [dbo].[UpdateActivityNews]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateActivityNews]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateActivityNews]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateActivityNews]
GO

CREATE PROCEDURE [dbo].[UpdateActivityNews]
	@Id int,
	@Title nvarchar(255),
	@Introduction nvarchar(4000),
	@Body nvarchar(max),
	@StartTime datetime,
	@EndTime datetime,
	@area nvarchar(255),
	@Dimensions nvarchar(255),
	@HasImage int,
	@ImagePath nvarchar(255),
	@Keyword nvarchar(50),
	@Source nvarchar(50),
	@UpdateTime datetime
AS

UPDATE [ActivityNews] SET
	[Title] = @Title,
	[Introduction] = @Introduction,
	[Body] = @Body,
	[StartTime] = @StartTime,
	[EndTime] = @EndTime,
	[area] = @area,
	[Dimensions] = @Dimensions,
	[HasImage] = @HasImage,
	[ImagePath] = @ImagePath,
	[Keyword] = @Keyword,
	[Source] = @Source,
	[UpdateTime] = @UpdateTime
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[DelActivityNews]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelActivityNews]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelActivityNews]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelActivityNews]
GO

CREATE PROCEDURE [dbo].[DelActivityNews]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[ActivityNews]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetActivityNews]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetActivityNews]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetActivityNews]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetActivityNews]
GO

CREATE PROCEDURE [dbo].[GetActivityNews]
	@Id int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Id],
	[Title],
	[Introduction],
	[Body],
	[StartTime],
	[EndTime],
	[area],
	[Dimensions],
	[HasImage],
	[ImagePath],
	[Keyword],
	[Source],
	[UpdateTime]
FROM
	[ActivityNews]
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[GetActivityNewsList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetActivityNewsList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetActivityNewsList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetActivityNewsList]
GO

CREATE PROCEDURE [dbo].[GetActivityNewsList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [ActivityNews]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[Id],
	[Title],
	[Introduction],
	[Body],
	[StartTime],
	[EndTime],
	[area],
	[Dimensions],
	[HasImage],
	[ImagePath],
	[Keyword],
	[Source],
	[UpdateTime]
FROM
	[ActivityNews]
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	WHERE [Id] < 
	( 
	SELECT MIN([Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Id] 
	FROM [ActivityNews] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


